package com.mes.service;

import com.mes.entity.EmesoJobOrderExe;
import com.yy.base.common.dao.Dao;
import com.yy.base.util.ObjectUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;

@Service
public class QualityService {
	@Autowired
	private Dao dao;
	/**
	 * 获取不合格原因
	 */
	public List<Map<String, Object>> getReasonList() {
		String sql = "select code, name from Emesq_Quality_Poor_Reason where isactive='Y'";
		return dao.query(sql);
	}
	/**
	 * 获取数据选项列表
	 */
	public List<Map<String, Object>> getSelectorList(String id) {
		String sql = "select value, name from ad_ref_list where ad_reference_id = ?";
		return dao.query(sql, id);
	}
	/**
	 * 获取待检物料清单
	 */
	public List<EmesoJobOrderExe> getJobExeList(String id) {
		String sql = "select " +
				"	e.emeso_manuorder_job_exe_id id," +
				"	e.code," +
				"	e.qty," +
				"	e.status," +
				"	e.quality_result result," +
				"	e.ad_user_id," +
				"	(select name from ad_user u where u.ad_user_id = e.ad_user_id) userName," +
				"	e.exe_Date_To exeDateTo," +
				"	e.code_Start as codeStart," +
				"	e.code_End as codeEnd " +
				" from emeso_manuorder_job_exe e " +
				"where e.status = 'CREATE'" +
				"  and e.emeso_manuorder_job_order_id = ?";
		return ObjectUtil.listMapToEntity(EmesoJobOrderExe.class, dao.query(sql, id));
	}
	/**
	 * 提交质量判定结果
	 */
	public void saveQualityResult(Map<String, String> map) {
		String sql = "{call Emes_Quality_Judgment(?, ?, ?, ?, ?, ?, ?, 'Emeso_MANUORDER_JOB_EXE')}";
		dao.executeSql(sql, map.get("code"), map.get("result"), map.get("reason"), 
				map.get("description"), map.get("userId"), map.get("clientId"), map.get("orgId"));
	}
	
	/**
	 * 根据用户ID获取当前工序号
	 */
	public String getProcessId(String id){
		String sql = "select p.emes_process_id processId from emes_process_route pr, emes_process p" +
				" where pr.emes_process_route_id = p.emes_process_route_id" +
				"   and pr.ad_org_id = ?" +
				"   and p.route_no = 60";
		Map<String,Object> resultMap = dao.queryUnique(sql, id);
		if (resultMap == null) {
			throw new RuntimeException("您所在的组织机构无工艺路线，请确定登录帐号是否有误！");
		}
		return (String) resultMap.get("PROCESSID");
	}
	
	/**
	 * 根据设备编号获取主键ID
	 */
	public String getFacilitiesId(String code){
		String sql = "select Eam_Facilities_Id as ID from Eam_Facilities where code = ? and status = 'USE'";
		Map<String,Object> resultMap = dao.queryUnique(sql, code);
		return null == resultMap ? null : (String)resultMap.get("ID");
	}
	
	/**
	 * 根据设备编号以及类型获取主键ID
	 */
	public String getFacilitiesId(String code,String fancility_type){
		String sql = "select Eam_Facilities_Id as ID from Eam_Facilities where code = ? and fancility_type=?  and status = 'USE'";
		Map<String,Object> resultMap = dao.queryUnique(sql, code,fancility_type);
		return null == resultMap ? null : (String)resultMap.get("ID");
	}
	
	/**
	 * 获取上料前的石英舟列表信息
	 */
	public List<Map<String,Object>> getBeforeFacilitiesIdList(){
		String sql = "select EF.Eam_Facilities_ID AS ID ,EF.CODE AS CODE from Eam_Facilities EF "
					+"WHERE EF.STATUS = 'USE' AND EF.fancility_type = 'SYZ' AND NOT EXISTS "
					+"(SELECT 1 FROM Emeso_Process_Conveyance EPC WHERE EPC.STATUS IN('created', 'production') AND EPC.Eam_Facilities_Id = EF.Eam_Facilities_ID) "
					+"ORDER BY EF.CODE ASC  ";
		return dao.query(sql);
	}
	
	/**
	 * 退火上料
	 */
	public int addProcessConveyance(Map<String, String> map){
		Map<String, Object> result = this.getProcessConveyance("created", map.get("Emes_Process_Id"), map.get("Eam_Facilities_Id"));
		int count = -1;
		if(null == result) {
			Map<String, Object> result2 = this.getProcessConveyance("production", map.get("Emes_Process_Id"), map.get("Eam_Facilities_Id"));
			if(result2 != null) {
				count = -2;
				return count;
			}
			String sql = "insert into Emeso_Process_Conveyance (Emeso_Process_Conveyance_Id, Emes_Process_Id, Eam_Facilities_Id, " +
					" IN_Conveyance1_ID, IN_Conveyance2_ID,Status,ad_client_id,ad_org_id,isactive,created,Add_Time,createdby,updated,updatedby) " +
					"values(sys_guid(), ?,?,?,?,?,?,?,'Y',now(),now(),?,now(),?)";
			count = dao.executeSql(sql, map.get("Emes_Process_Id"),map.get("Eam_Facilities_Id"),map.get("Conveyance1_ID"),map.get("Conveyance2_ID")
					,map.get("status"),map.get("clientId"),map.get("orgId"),map.get("createdby"),map.get("createdby"));
		}
		return count;
	}
	
	public Map<String, Object> getProcessConveyance(String status, String processId, String facilitiesId) {
		String sql = "select Emeso_Process_Conveyance_Id, Emeso_Manu_Batch_Id from Emeso_Process_Conveyance " +
				"where status = ?" +
				"  and Emes_Process_Id = ? " +
				"  and Eam_Facilities_Id = ?";
		return dao.queryUnique(sql, status, processId, facilitiesId);
	}
	
	/**
	 * 获取进退火炉前的石英舟列表信息
	 */
	public List<Map<String,Object>> getMiddleFacilitiesIdList(){
		String sql = "select EF.Eam_Facilities_ID AS ID ,EF.CODE AS CODE from Eam_Facilities EF "
					+"WHERE EF.STATUS = 'USE' AND EF.fancility_type = 'SYZ' AND EXISTS "
					+"(SELECT 1 FROM Emeso_Process_Conveyance EPC WHERE EPC.STATUS = 'created' AND EPC.Eam_Facilities_Id = EF.Eam_Facilities_ID) "
					+"ORDER BY EF.CODE ASC  ";
		return dao.query(sql);
	}

	/**
	 * 退火中
	 */
	public int modifyProcessConveyance(Map<String, String> map){
		Map<String, Object> pcMap = this.getProcessConveyance("created", (String)map.get("Emes_Process_Id"), (String)map.get("Eam_Facilities_Id"));
		if (pcMap == null) {
			throw new RuntimeException("当前石英舟无未进炉记录！");
		}
		
		String sql = "update Emeso_Process_Conveyance set Manu_Facilities_Id=?,In_Time=now(),Status=?,updated=now(),updatedby=?" +
				" where status = 'created' and Emeso_Process_Conveyance_ID=? ";
		return dao.executeSql(sql, map.get("Manu_Facilities_Id"), map.get("status"), map.get("updatedby"), pcMap.get("Emeso_PROCESS_CONVEYANCE_ID"));
	}
	
	
	
	/**
	 * 获取下料前的石英舟列表信息
	 */
	public List<Map<String,Object>> getAfterFacilitiesIdList(){
		String sql = "select EF.Eam_Facilities_ID AS ID ,EF.CODE AS CODE from Eam_Facilities EF "
					+"WHERE EF.STATUS = 'USE' AND EF.fancility_type = 'SYZ' AND EXISTS "
					+"(SELECT 1 FROM Emeso_Process_Conveyance EPC " +
					"   WHERE EPC.STATUS = 'production' AND EPC.Eam_Facilities_Id = EF.Eam_Facilities_ID) "
					+"ORDER BY EF.CODE ASC  ";
		return dao.query(sql);
	}

	/**
	 * 退火下料
	 */
	public int updateProcessConveyance(Map<String, Object> map){
		Map<String, Object> pcMap = this.getProcessConveyance("production", (String)map.get("Emes_Process_Id"), (String)map.get("Eam_Facilities_Id"));
		if (pcMap == null) {
			throw new RuntimeException("当前石英舟未进行生产，请先进炉生产！");
		}
		
		String sql = "update Emeso_Process_Conveyance " +
				" set Out_Conveyance1_ID=?,Out_Conveyance2_ID=?,Out_Time=now(),Status=?,updated=now(),updatedby=?" +
				" where status = 'production' and Emes_Process_Id = ? and Eam_Facilities_Id=? ";
		int result = dao.executeSql(sql, map.get("Conveyance1_ID"),map.get("Conveyance2_ID"),map.get("status")
				,map.get("updatedby"),map.get("Emes_Process_Id"),map.get("Eam_Facilities_Id"));
		
		map.put("Emeso_Manu_Batch_Id", pcMap.get("Emeso_MANU_BATCH_ID"));
		
		//已取消下料时录入碎片
		//updateProcessConveyanceProcedures(map);//计算碎片
		return result;
	}
	
	/**
	 * 获取退火记录
	 * @return 
	 */
	public List<Map<String, Object>> getConveyanceList(String orgId) {
		String sql = "select pc.Emeso_PROCESS_CONVEYANCE_ID " +
				"       ,decode(pc.status, 'created', '上料', 'production', '进炉', 'complete', '下料') status " + 
				"       ,(select f.code from Eam_Facilities f where pc.Eam_Facilities_id=f.Eam_Facilities_id) syzcode " +
				"       ,(select f.name from Eam_Facilities f where pc.Eam_Facilities_id=f.Eam_Facilities_id) syzname " +
				"       ,(select f.code from Eam_Facilities f where pc.IN_CONVEYANCE1_ID=f.Eam_Facilities_id) inhl1 " +
				"       ,(select f.code from Eam_Facilities f where pc.IN_CONVEYANCE2_ID=f.Eam_Facilities_id) inhl2 " +
				"       ,(select f.code from Eam_Facilities f where pc.Manu_Facilities_Id=f.Eam_Facilities_id) jlthl " +
				"       ,(select f.code from Eam_Facilities f where pc.OUT_CONVEYANCE1_ID=f.Eam_Facilities_id) outhl1 " +
				"       ,(select f.code from Eam_Facilities f where pc.OUT_CONVEYANCE2_ID=f.Eam_Facilities_id) outhl2 " +
				"       ,to_char(pc.add_time, 'yyyy-MM-dd hh24:mi:ss') addtime " + 
				"       ,to_char(pc.in_time, 'yyyy-MM-dd hh24:mi:ss') intime " + 
				"       ,to_char(pc.out_time, 'yyyy-MM-dd hh24:mi:ss') outtime " + 
				"       ,(select u.name from ad_user u where pc.updatedby=u.ad_user_id) optname " + 
				" from Emeso_Process_Conveyance pc " + 
				" where 1=1 and pc.ad_org_id=? " +
				" order by pc.add_time desc ";
		List<Map<String, Object>> data = this.dao.query(sql, orgId);
		return data;
	}
	
	/**
	 * 退火下料计算碎片
	public int updateProcessConveyanceProcedures(Map<String, Object> map){
		String sql = "{call Mes_DataCollection.scrap(?, null, ?, ?, ?)}";
		String fragmentQty = (String)map.get("fragmentQty");
		int qty = 0;
		if(null != fragmentQty){
			if(fragmentQty.matches("[0-9]*")){
				qty = Integer.parseInt(fragmentQty);
			}
		}
		if (qty > 0) {
			return dao.executeSql(sql, map.get("Emes_Process_Id"), qty, map.get("updatedby"), map.get("Emeso_Manu_Batch_Id"));
		}
		return 0;
	}
	 */
}